#!/usr/bin/python
#coding=utf-8

from sqlalchemy.types import VARCHAR, DECIMAL
import pandas as pd
import datetime
# from common import df_operator as op, db_utils as util
from common import db_utils as util
import sys


dtgC55s = """
    CREATE TABLE BGTARAS1.PREQ512_DTG_C55S(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PROD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        HUMI1_DEW_SV DECIMAL(24,6),
        HUMI2_DEW_SV DECIMAL(24,6),
        HUMI3_DEW_SV DECIMAL(24,6),
        HUMI4_DEW_SV DECIMAL(24,6),
        MIX1_H2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX2_H2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX3_H2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX4_H2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX5_H2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX6_H2_PIPE_FLOW_PV DECIMAL(24,6),
        HUMI1_STREAM_OPEN_PV DECIMAL(24,6),
        HUMI2_STREAM_OPEN_PV DECIMAL(24,6),
        HUMI3_STREAM_OPEN_PV DECIMAL(24,6),
        HUMI4_STREAM_OPEN_PV DECIMAL(24,6),
        MIX1_N2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX2_N2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX3_N2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX4_N2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX5_N2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX6_N2_PIPE_FLOW_PV DECIMAL(24,6),
        H2_RECYC_FLOW_PV_AVG DECIMAL(24,6),
        ESD1_SEAL_N2_FLOW_AVG DECIMAL(24,6),
        EXT_SEAL_N2_FLOW_AVG DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""



def createDTG_C55S(start_time,end_time):
    sql = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ512_DTG_C55S' AND TABSCHEMA='BGTARAS1'"""
    sql_result = util.query(conn_rds, sql=sql)
    if sql_result.__len__() > 0 :
        conn_rds.execute("drop table BGTARAS1.PREQ512_DTG_C55S")
    else:
        conn_rds.execute(dtgC55s)

    sql = """WITH
    TMP AS
    (
        SELECT
            A.UNIT_CODE,
            A.CLASS,
            A.SHIFT,
            A.PRODUCE_TIME,
            A.PROD_COILNO,
            A.ENTRY_COILNO,
            CONCAT(B.START_TIME_DATE, B.START_TIME_TIME) AS START_TIME,
            CONCAT(B.END_TIME_DATE, B.END_TIME_TIME)     AS END_TIME
        FROM
            BGTARAS1.T_ADS_FACT_SICB_CD0201 A,
            BGTAROQ512.T_ODS_DTG_DCDT B
        WHERE
            a.UNIT_CODE='Q512'
        AND A.COST_CENTER='MIJA'
        AND b.PRD_COILNO=a.PROD_COILNO
        AND a.PRODUCE_TIME BETWEEN """+start_time+""" AND """+end_time+"""
    )
SELECT
    A.UNIT_CODE,
    A.CLASS,
    A.SHIFT,
    A.PRODUCE_TIME,
    A.PROD_COILNO,
    A.ENTRY_COILNO,
    AVG(CAST(C.HUMI1_DEW_SV AS DOUBLE)/10)                                 AS HUMI1_DEW_SV,--加湿器露点设定
    AVG(CAST(C.HUMI2_DEW_SV AS DOUBLE)/10)                                          AS HUMI2_DEW_SV,
    AVG(CAST(C.HUMI3_DEW_SV AS DOUBLE)/10)                                          AS HUMI3_DEW_SV,
    AVG(CAST(C.HUMI4_DEW_SV AS DOUBLE)/10)                                          AS HUMI4_DEW_SV,
    AVG(CAST(C.mix1_h2_pipe_flow_pv AS DOUBLE)/(C.mix1_n2h2_flow_rtf1_pv+C.mix1_n2h2_flow_sf1_pv))*10 AS
                                                                      MIX1_H2_PIPE_FLOW_PV,--混气站氢气浓度
    AVG(CAST(C.mix2_h2_pipe_flow_pv AS DOUBLE)/C.mix2_n2h2_flow_pv)*100 AS MIX2_H2_PIPE_FLOW_PV,
    AVG(CAST(C.mix3_h2_pipe_flow_pv AS DOUBLE)/C.mix3_n2h2_flow_pv)*100 AS MIX3_H2_PIPE_FLOW_PV,
    AVG(CAST(C.mix4_h2_pipe_flow_pv AS DOUBLE)/C.mix4_n2h2_flow_pv)*100 AS MIX4_H2_PIPE_FLOW_PV,
    AVG(CAST(C.mix5_h2_pipe_flow_pv AS DOUBLE)/C.mix5_n2h2_flow_pv)*100 AS MIX5_H2_PIPE_FLOW_PV,
    AVG(CAST(C.mix6_h2_pipe_flow_pv AS DOUBLE)/C.mix6_n2h2_flow_pv)*100 AS MIX6_H2_PIPE_FLOW_PV,
    AVG(CAST(C.HUMI1_STREAM_OPEN_PV AS DOUBLE)/10)                  AS HUMI1_STREAM_OPEN_PV,--
    -- 加湿器露点蒸汽阀门开度
    AVG(CAST(C.HUMI2_STREAM_OPEN_PV AS DOUBLE)/10) AS HUMI2_STREAM_OPEN_PV,
    AVG(CAST(C.HUMI3_STREAM_OPEN_PV AS DOUBLE)/10) AS HUMI3_STREAM_OPEN_PV,
    AVG(CAST(C.HUMI4_STREAM_OPEN_PV AS DOUBLE)/10) AS HUMI4_STREAM_OPEN_PV,
    AVG(CAST(C.MIX1_N2_PIPE_FLOW_PV AS DOUBLE)/10) AS MIX1_N2_PIPE_FLOW_PV,--混气站氮气流量
    AVG(CAST(C.MIX2_N2_PIPE_FLOW_PV AS DOUBLE)/10) AS MIX2_N2_PIPE_FLOW_PV,
    AVG(CAST(C.MIX3_N2_PIPE_FLOW_PV AS DOUBLE)/10) AS MIX3_N2_PIPE_FLOW_PV,
    AVG(CAST(C.MIX4_N2_PIPE_FLOW_PV AS DOUBLE)/10) AS MIX4_N2_PIPE_FLOW_PV,
    AVG(CAST(C.MIX5_N2_PIPE_FLOW_PV AS DOUBLE)/10) AS MIX5_N2_PIPE_FLOW_PV,
    AVG(CAST(C.MIX6_N2_PIPE_FLOW_PV AS DOUBLE)/10) AS MIX6_N2_PIPE_FLOW_PV,
    AVG(CAST(D.H2_RECYC_FLOW_PV AS DOUBLE)) AS H2_RECYC_FLOW_PV_AVG,
    AVG(CAST(D.ESD1_SEAL_N2_FLOW AS DOUBLE)) AS ESD1_SEAL_N2_FLOW_AVG,
    AVG(CAST(D.EXT_SEAL_N2_FLOW AS DOUBLE)/10) AS EXT_SEAL_N2_FLOW_AVG
FROM
    TMP a
LEFT JOIN
    BGTAROQ512.T_ODS_DTG_C55S C
ON
    c.UP_DATE BETWEEN a.START_TIME AND a.END_TIME
LEFT JOIN
    BGTAROQ512.T_ODS_DTG_C65S D
ON
    D.UP_DATE BETWEEN a.START_TIME AND a.END_TIME
GROUP BY
    A.UNIT_CODE,
    A.CLASS,
    A.SHIFT,
    A.PRODUCE_TIME,
    A.PROD_COILNO,
    A.ENTRY_COILNO
    """
    df_result = util.query(conn_rds, sql=sql)
    util.insert(df_result, conn_rds, "PREQ512_DTG_C55S", "BGTARAS1")


def PreQ512(start_time, end_time):
    global conn_rds
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    createDTG_C55S(start_time, end_time)
    conn_rds.close()

def main():
    PreQ512('20211015185035','20211015185035')

if __name__ == '__main__':
    main()